const Router = require('koa-router')
// todo mysql2 module to refactor the promise
const mysql = require('mysql')
const mysql2 = require('mysql2')
const Sequelize = require('sequelize')
const thunkify = require('thunkify')

// simulate the current login user id
// via command line argv
let uid = process.env.UID || 13

const conn = mysql.createConnection({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
})

const sqlQuery = thunkify(conn.query.bind(conn))

const router = new Router({
  prefix: '/api',
})

router.get('/msgs', function* () {
  let sql = 'SELECT userid,username FROM yunsupply_member WHERE userid=?'
  const user = yield sqlQuery(sql, [uid])
  let username = user[0][0].username
  sql = `
    SELECT itemid,title,content,fromuser,touser,flag
    FROM yunsupply_message
    WHERE touser=? AND typeid!=4
    ORDER BY itemid DESC
  `
  let msgList = yield sqlQuery(sql, [username])

  let output = []
  msgList[0].forEach((value) => {
    let obj = {}
    obj.id = value.itemid
    obj.title = value.title

    let pattern = /<strong>(.*)<\/strong>[\s\S]*联系人：(.*?)<br\/>/
    let arr = pattern.exec(value.content)
    obj.productName = arr[1]
    obj.surname = arr[2]

    pattern = /(\d{4}\-\d{2}\-\d{2})/
    arr = pattern.exec(value.content)
    obj.waati = arr[1]

    output.push(obj)
  })

  this.body = output
})

router.get('/messages', function* () {
  // with mysql2 module connection with promise
  const conn = yield mysql2.createConnectionPromise({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
  })
  let sql = 'SELECT userid,username FROM yunsupply_member WHERE userid=?'
  const user = yield conn.execute(sql, [uid])
  // console.log('node module mysql -> RowDataPacket\n', (yield sqlQuery(sql, [uid]))[0][0])
  // console.log()
  // console.log('node module mysql2 -> TextRow\n', (yield conn.query(sql, [uid]))[0]
  //   [0])
  // console.log()
  // console.log('node module mysql2 -> BinaryRow\n', (yield conn.execute(sql, [uid]))[0][0])
  sql = `
    SELECT itemid,title,content,fromuser,touser,flag
    FROM yunsupply_message
    WHERE touser=? AND typeid!=4
    ORDER BY itemid DESC
  `
  let username = user[0][0].username
  const msgList = yield conn.execute(sql, [username])

  let output = []
  msgList[0].forEach((value) => {
    let obj = {}
    obj.id = value.itemid
    obj.title = value.title

    let pattern = /<strong>(.*)<\/strong>[\s\S]*联系人：(.*?)<br\/>/
    let arr = pattern.exec(value.content)
    obj.productName = arr[1]
    obj.surname = arr[2]

    pattern = /(\d{4}\-\d{2}\-\d{2})/
    arr = pattern.exec(value.content)
    obj.waati = arr[1]

    output.push(obj)
  })

  this.body = output
})

router.get('/tidings', function* () {
  const sequelize = new Sequelize(
    process.env.MYSQL_DATABASE,
    process.env.MYSQL_USER,
    process.env.MYSQL_PASSWORD, {
      host: process.env.MYSQL_HOST,
      pool: {
        max: 5,
        min: 0,
        idle: 10000,
      },
  })
  let sql = 'SELECT userid,username FROM yunsupply_member WHERE userid=:uid'
  const user = yield sequelize.query(sql, {
    replacements: { uid: uid }, type: sequelize.QueryTypes.SELECT
  })
  let username = user[0].username
  sql = `
    SELECT itemid,title,content,fromuser,touser,flag
    FROM yunsupply_message
    WHERE touser=:username AND typeid!=4
    ORDER BY itemid DESC
  `
  const msgList = yield sequelize.query(sql, {
    replacements: { username: username }, type: sequelize.QueryTypes.SELECT
  })
  let output = []
  msgList.forEach((value) => {
    let obj = {}
    obj.id = value.itemid
    obj.title = value.title

    let pattern = /<strong>(.*)<\/strong>[\s\S]*联系人：(.*?)<br\/>/
    let arr = pattern.exec(value.content)
    obj.productName = arr[1]
    obj.surname = arr[2]

    pattern = /(\d{4}\-\d{2}\-\d{2})/
    arr = pattern.exec(value.content)
    obj.waati = arr[1]

    output.push(obj)
  })
  this.body = output
})

// router.put('/msgs/:id', function* () {
router.patch('/msgs/:id', function* () {
  const id = this.params.id
  let sql = 'SELECT itemid,title,flag FROM yunsupply_message WHERE itemid=?'
  let rs = yield sqlQuery(sql, [id])
  const currentFlag = rs[0][0].flag
  const toggleVal = Number(!currentFlag)
  sql = 'UPDATE yunsupply_message SET flag=? WHERE itemid=?'
  let updateRs = yield sqlQuery(sql, [toggleVal, id])
  if (updateRs[0].affectedRows === 1) {
    this.body = rs[0][0]
    return
  }
  this.body = { msg: 'May be something wrong or nothing happened' }
})

module.exports = router
